﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

using RL.ObjectLayer;

namespace RL.DataAccessLayer
{
    public class MemberDAL
    {
        private DBConn _dbConnect;

        public MemberDAL()
        {
            _dbConnect = new DBConn();
        }

        public List<DivisionOL> LoadDivision_DropDwonList()
        {
            SqlConnection connection = null;
            DataSet ds = null;
            DivisionOL division = null;
            List<DivisionOL> divisionList = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("GetUser", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                ds = new DataSet();
                _dbConnect.CommitTransaction();
                da.Fill(ds);
                _dbConnect.CloseConnection();

                divisionList = new List<DivisionOL>();

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    division = new DivisionOL();
                    division.DivisionID = int.Parse(row["DivisionID"].ToString());
                    division.Division = row["Division"].ToString();
                    divisionList.Add(division);
                }

                return divisionList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
        }

        public void RegisterMember(MemberOL _memberOL, List<FCMemberOL> _lstfcmemberOL)
        {
            SqlConnection connection = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();

                SqlCommand insertcommand = new SqlCommand("Member_Save", connection);
                insertcommand.CommandType = CommandType.StoredProcedure;
                insertcommand.Parameters.Add("@LocalID", SqlDbType.NVarChar).Value = _memberOL.LocalID;
                insertcommand.Parameters.Add("@FollowersID", SqlDbType.NVarChar).Value = _memberOL.FollowersID;
                insertcommand.Parameters.Add("@ConnectionTypeID", SqlDbType.Int).Value = _memberOL.ConnectionTypeID;
                insertcommand.Parameters.Add("@FamilyConnectionID", SqlDbType.Int).Value = _memberOL.FamilyConnectionID;
                insertcommand.Parameters.Add("@RelationshipTypeID", SqlDbType.Int).Value = _memberOL.RelationshipTypeID;
                insertcommand.Parameters.Add("@DOC", SqlDbType.DateTime).Value = _memberOL.DOC;
                insertcommand.Parameters.Add("@DivisionID", SqlDbType.Int).Value = _memberOL.DivisionID;
                insertcommand.Parameters.Add("@LineageParentID", SqlDbType.Int).Value = _memberOL.LineageParentID;
                insertcommand.Parameters.Add("@Surname", SqlDbType.NVarChar).Value = _memberOL.Surname;
                insertcommand.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = _memberOL.FirstName;
                insertcommand.Parameters.Add("@DOB", SqlDbType.DateTime).Value = _memberOL.DOB;
                insertcommand.Parameters.Add("@PhoneNo", SqlDbType.NVarChar).Value = _memberOL.PhoneNo;
                insertcommand.Parameters.Add("@GenderID", SqlDbType.Int).Value = _memberOL.GenderID;
                insertcommand.Parameters.Add("@CitizenshipID", SqlDbType.Int).Value = _memberOL.CitizenshipID;
                insertcommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = _memberOL.CountryID;
                insertcommand.Parameters.Add("@ZIPPostalID", SqlDbType.Int).Value = _memberOL.ZIPPostalID;
                insertcommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = _memberOL.Address;
                insertcommand.Parameters.Add("@CityTown", SqlDbType.NVarChar).Value = _memberOL.CityTown;
                insertcommand.Parameters.Add("@Province", SqlDbType.NVarChar).Value = _memberOL.Province;
                insertcommand.Parameters.Add("@GuidingParent", SqlDbType.NVarChar).Value = _memberOL.GuidingParent;
                insertcommand.Parameters.Add("@HOHGuidingParent", SqlDbType.NVarChar).Value = _memberOL.HOHGuidingParent;
                insertcommand.Parameters.Add("@IDNo", SqlDbType.NVarChar).Value = _memberOL.IDNo;
                insertcommand.Parameters.Add("@IUserID", SqlDbType.Int).Value = insertcommand.Parameters.Add("@UUserID", SqlDbType.Int).Value = _memberOL.IUserID;

                _dbConnect.CommitTransaction();
                int memID = Convert.ToInt32(insertcommand.ExecuteScalar());

                foreach (var row in _lstfcmemberOL)
                {
                    _dbConnect.BeginTransaction();
                    insertcommand = new SqlCommand("Member_Save", connection);
                    insertcommand.CommandType = CommandType.StoredProcedure;
                    insertcommand.Parameters.Add("@LocalID", SqlDbType.NVarChar).Value = _memberOL.LocalID;
                    insertcommand.Parameters.Add("@FollowersID", SqlDbType.NVarChar).Value = memID;
                    insertcommand.Parameters.Add("@ConnectionTypeID", SqlDbType.Int).Value = _memberOL.ConnectionTypeID;
                    insertcommand.Parameters.Add("@FamilyConnectionID", SqlDbType.Int).Value = _memberOL.FamilyConnectionID;
                    insertcommand.Parameters.Add("@RelationshipTypeID", SqlDbType.Int).Value = _memberOL.RelationshipTypeID;
                    insertcommand.Parameters.Add("@DOC", SqlDbType.DateTime).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@DivisionID", SqlDbType.Int).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@LineageParentID", SqlDbType.Int).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@Surname", SqlDbType.NVarChar).Value = row.Surname;
                    insertcommand.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = row.FirstName;
                    insertcommand.Parameters.Add("@DOB", SqlDbType.DateTime).Value = row.DOB;
                    insertcommand.Parameters.Add("@PhoneNo", SqlDbType.NVarChar).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@GenderID", SqlDbType.Int).Value = row.GenderID;
                    insertcommand.Parameters.Add("@CitizenshipID", SqlDbType.Int).Value = row.CitizenshipID;
                    insertcommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@ZIPPostalID", SqlDbType.Int).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@CityTown", SqlDbType.NVarChar).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@Province", SqlDbType.NVarChar).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@GuidingParent", SqlDbType.NVarChar).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@HOHGuidingParent", SqlDbType.NVarChar).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@IDNo", SqlDbType.NVarChar).Value = DBNull.Value;
                    insertcommand.Parameters.Add("@IUserID", SqlDbType.Int).Value = insertcommand.Parameters.Add("@UUserID", SqlDbType.Int).Value = _memberOL.IUserID;

                    _dbConnect.CommitTransaction();
                    insertcommand.ExecuteScalar();

                }
                _dbConnect.CloseConnection();


            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
        }
    }
}
